﻿# encoding:utf-8
# 访问数据库 用户,这里把所有的用户、群、好友等角色都当成 User 保存
from wxpyImport import *
from UniqueDbBase import *
    
# isGroup: True False
def dbGetUserAll(dbd, puid, isGroup):
    if puid is None:
        logError('dbGetUserAl puid empty')
        return
    
    groupStr = '1' if isGroup else '0'

    sql = "select * from user where puid = %s and is_group="+groupStr+" order by update_time desc"

    results = None
    try:
        cursor = getCursorColName(dbd)
        cursor.execute(sql,(puid))
        results = cursor.fetchall()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbGetUserAl err: '+str(e))
    
    return results
        
def dbGetUserBig(dbd, puid, resetDb=False):
    sql = "select * from user_big where puid = %s order by update_time desc limit 1"

    results = None
    try:
        cursor = getCursor(dbd, resetDb)
        cursor.execute(sql,(puid))
        results = cursor.fetchone()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbGetUserBig err: '+str(e))
        if not resetDb: # 防止死循环
            return dbGetUserBig(dbd, puid, True)
    finally:
        return results

# 保存用户信息，puid 存在则更新，不存在新增
def dbAddUser(dbd, user, resetDb=False):
    if user.puid is None:
        logError('dbAddUse puid empty: '+str(user))
        try: # 防止 user.raw 不存在
            logError('dbAddUse puid empty2: '+str(user.raw))
        except Exception as e:
            dealException()
            logError('dbAddUs err: '+str(e))
        
        return
    
    isgroup = 1 if isGroup(user) else 0
    userOld = dbGetUser(dbd, user.puid)
    if userOld is not None:
        if userOld['is_group']!=isgroup: # 假如用户与群 puid 相同，则保存两个用户
            userOld = None
    userOldBig = dbGetUserBig(dbd, user.puid)
    sql = "update user set nick_name = %s, remark_name = %s, avatar = '' where is_group = %s and puid = %s order by update_time desc limit 1"
    # sql = "update user set nick_name = %s, remark_name = %s, is_group = %s, avatar = '' where puid = %s "
    sqlBig = "update user_big set raw = %s where puid = %s order by create_time desc limit 1"

    # 某些消息昵称是空？还是个人设置没有昵称?
    nick_name = user.nick_name
    if nick_name is None:
        logError('nick_name empty: '+str(user))
        nick_name = ''
    remark_name = ''
    if not isGroup(user):
        remark_name = user.remark_name
        if remark_name is None:
            remark_name = ''
    
    if userOld is None:
        sql = "INSERT INTO user (nick_name, remark_name, is_group, puid) VALUES(%s, %s, %s, %s)"
    else: # 如果最新的值为空而数据库中有值，则保留数据库中的值
        if '' == nick_name:
            nick_name = userOld['nick_name']
        if '' == remark_name:
            remark_name = userOld['remark_name']
            
    if userOldBig is None:
        sqlBig = "INSERT INTO user_big (raw, puid) VALUES(%s, %s)"

    try:
        cursor = getCursor(dbd, resetDb)
        # 存大块聊天记录
        cursor.execute(sql,(nick_name, remark_name, isgroup, user.puid))
        cursor.execute(sqlBig,(str(user.raw), user.puid))
        dbd.db.commit()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbAddUse err: '+str(e))
        logError('dbAddUse err nick_name len: '+str(len(nick_name)))
        logError('dbAddUse err remark_name len: '+str(len(remark_name)))
        if not resetDb: # 防止死循环
            return dbAddUser(dbd, user, True)
    else:
        logError('add user success: ' + str(user.puid))

# 更新用户头像
def dbUpdateUserAvatar(dbd, puid, avatarUrl):
    if puid is None:
        logError('dbUpdateUserAvata puid empty')
        return

    sql = "update user u set u.avatar = %s where u.puid = %s order by u.create_time desc limit 1"

    try:
        cursor = getCursor(dbd)
        cursor.execute(sql,(avatarUrl, puid))
        dbd.db.commit()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbUpdateUserAvata err: '+str(e))

# 更新用户数据 by id
# attrs 要改变的属性名 list
def dbUpdateUserById(dbd, user, attrs):
    sql = 'update user u set '
    for i, a in enumerate(attrs):
        if 0==i:
            sql = sql + ' u.' + a + '=\'' + str(user[a]) + '\''
        else:
            sql = sql + ', u.' + a + '=\'' + str(user[a]) + '\''
    
    sql = sql + ' where u.id=%s'

    try:
        cursor = getCursor(dbd)
        cursor.execute(sql,(user['id']))
        dbd.db.commit()
        cursor.close()
        return True
    except Exception as e:
        dealException()
        logError('dbUpdateUserByI err: '+str(e))
        return False

# 取同一 puid 中某个属性最新的值
def dbGetUserAttributeLatest(dbd, puid, attributeName):
    #sql = "select * from user where puid = %s and %s <> \'\' order by update_time desc limit 1"
    sql = "select * from user where puid = %s and " + attributeName + "<>\'\' order by update_time desc limit 1"

    try:
        cursor = getCursorColName(dbd)
        cursor.execute(sql,(puid))
        result = cursor.fetchone()
        cursor.close()
        if(result==None):
            return None
        
        return result[attributeName]
    except Exception as e:
        dealException()
        logError('dbGetUserAl err: '+str(e))
    
    return None

# 删除用户，存到删除表中
def dbDelUser(dbd, id):
    #sql = "select * from user where puid = %s and %s <> \'\' order by update_time desc limit 1"
    sql = "select * from user where puid = %s and " + attributeName + "<>\'\' order by update_time desc limit 1"

    try:
        cursor = getCursorColName(dbd)
        cursor.execute(sql,(puid))
        result = cursor.fetchone()
        cursor.close()
        if(result==None):
            return None
        
        return result[attributeName]
    except Exception as e:
        dealException()
        logError('dbGetUserAl err: '+str(e))
    
    return None

# 往用户删除表加一条数据
def dbAddUserDel(dbd, user):
    sql = "INSERT INTO user_del (id, puid, nick_name, remark_name, avatar, is_group, create_time, update_time) " \
    "VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"

    try:
        cursor = getCursor(dbd)
        # 存大块聊天记录
        cursor.execute(sql,(user['id'], user['puid'], user['nick_name'], user['remark_name'], user['avatar'],
        user['is_group'], user['create_time'], user['update_time']))
        dbd.db.commit()
        cursor.close()
        return True
    except Exception as e:
        dealException()
        errStr = str(e)
        logError('dbAddUserDe err: '+errStr)
        # id 重复表明已插入，仍然算成功
        if '(1062, \"Duplicate entry' in errStr:
            return True

        return False

# 物理删除用户
def dbDelUser(dbd, id):
    sql = "DELETE FROM `user` WHERE  `id`=%s;"

    try:
        cursor = getCursor(dbd)
        cursor.execute(sql,(id))
        dbd.db.commit()
        cursor.close()
        return True
    except Exception as e:
        dealException()
        logError('dbDelUse err: '+str(e))
        return False

# 取所有重复的 puid
def dbGetAllRepeatPuids(dbd):
    sql = "SELECT u.puid FROM user u GROUP BY u.puid HAVING COUNT(*) > 1"

    results = None
    try:
        cursor = getCursor(dbd)
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()
    except Exception as e:
        dealException()
        logError('dbGetAllRepeatPuid err: '+str(e))
    finally:
        return results